package jsp;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class BookDao {
	
	public Connection getConnection() {
		Connection conn = null; // 鏁版嵁搴撹繛鎺�
		try {
			Class.forName("com.mysql.jdbc.Driver"); // 鍔犺浇鏁版嵁搴撻┍鍔紝娉ㄥ唽鍒伴┍鍔ㄧ鐞嗗櫒
			// 鏁版嵁搴撹繛鎺ュ瓧绗︿覆
			String url = "jdbc:mysql://localhost:3306/jsp";
			String username = "root"; // 鏁版嵁搴撶敤鎴峰悕
			String password = "123"; // 鏁版嵁搴撳瘑鐮�
			// 鍒涘缓Connection杩炴帴
			conn = DriverManager.getConnection(url, username, password);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn; // 杩斿洖鏁版嵁搴撹繛鎺�
	}


	public List<BookBean> find(int page) {
		List<BookBean> list = new ArrayList<>(); // 鍒涘缓List
		Connection conn = getConnection(); // 鑾峰彇鏁版嵁搴撹繛鎺�
		// 鍒嗛〉鏌ヨ鐨凷QL璇彞
		String sql = "select * from tb_books order by id desc limit ?,?";
		try {
			PreparedStatement ps = conn.prepareStatement(sql); // 鑾峰彇PreparedStatement
			ps.setInt(1, (page - 1) * BookBean.PAGE_SIZE); // 瀵筍QL璇彞涓殑绗�1涓弬鏁拌祴鍊�
			ps.setInt(2, BookBean.PAGE_SIZE); // 瀵筍QL璇彞涓殑绗�2涓弬鏁拌祴鍊�
			ResultSet rs = ps.executeQuery(); // 鎵ц鏌ヨ鎿嶄綔
			while (rs.next()) { // 鍏夋爣鍚戝悗绉诲姩锛屽苟鍒ゆ柇鏄惁鏈夋晥
				BookBean b = new BookBean(); // 瀹炰緥鍖朆ookBean
				b.setId(rs.getInt("id")); // 瀵筰d灞炴�ц祴鍊�
				b.setName(rs.getString("name")); // 瀵筺ame灞炴�ц祴鍊�
				b.setPrice(rs.getFloat("price")); // 瀵筽rice灞炴�ц祴鍊�
				b.setNumber(rs.getInt("number")); // 瀵筨ookCount灞炴�ц祴鍊�
				b.setAuthor(rs.getString("author")); // 瀵筧uthor灞炴�ц祴鍊�
				list.add(b); // 灏咮ookBean娣诲姞鍒癓ist闆嗗悎涓�
			}
			rs.close(); // 鍏抽棴ResultSet
			ps.close(); // 鍏抽棴PreparedStatement
			conn.close(); // 鍏抽棴Connection
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	public int findCount() {
		int count = 0; // 鎬昏褰曟暟
		Connection conn = getConnection(); // 鑾峰彇鏁版嵁搴撹繛鎺�
		String sql = "select count(*) from tb_books"; // 鏌ヨ鎬昏褰曟暟SQL璇彞
		try {
			Statement stmt = conn.createStatement(); // 鍒涘缓Statement
			ResultSet rs = stmt.executeQuery(sql); // 鏌ヨ骞惰幏鍙朢esultSet
			if (rs.next()) { // 鍏夋爣鍚戝悗绉诲姩锛屽苟鍒ゆ柇鏄惁鏈夋晥
				count = rs.getInt(1); // 瀵规�昏褰曟暟璧嬪��
			}
			rs.close(); // 鍏抽棴ResultSet
			conn.close(); // 鍏抽棴Connection
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return count; // 杩斿洖鎬昏褰曟暟
	}
}